﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.Serialization;
using Skywave.Linq;

namespace Skywave.Data
{

    [DataContract]
    public enum SqlJoinTypes
    {
        [EnumMember]
        Inner = 1,
        [EnumMember]
        LeftOuter = 2,
        [EnumMember]
        RightOuter = 3
    }

    public class SqlReport
    {
        public SqlReport()
        {
            SqlWhere = new List<string>();
            SqlJoin = new Dictionary<string, SqlJoinTypes>();
            SqlDeclare = new List<string>();
            Tags = new Dictionary<string, string>();
        }
        // Props
        public List<string> SqlDeclare { get; set; }
        public List<string> SqlWhere { get; set; }
        public string SqlFrom { get; set; }
        public Dictionary<string, SqlJoinTypes> SqlJoin { get; set; }
        public Dictionary<string, string> Tags { get; set; }
        public string SqlRandomKey { get; set; }
        public string SqlInitialization { get; set; }
        // Events
        public event EventHandler<SqlReportBeforeExecuteEventArgs> BeforeExecute;
        public event EventHandler<SqlReportNeedColumnDataEventArgs> NeedColumnData;

        public List<SqlReportRow> Execute(System.Data.SqlClient.SqlConnection cn1, List<SqlReportColumn> columns, Dictionary<SqlReportColumn, System.Data.SqlClient.SortOrder> order, int? rowNumberStart = null, int? rowNumberEnd = null)
        {
            return _Execute(cn1, columns, order, rowNumberStart, rowNumberEnd) as List<SqlReportRow>;
        }
        public int ExecuteCount(System.Data.SqlClient.SqlConnection cn1, List<SqlReportColumn> columns)
        {
            return (int)_Execute(cn1, columns, null, null, null, true);
        }

        private System.Text.RegularExpressions.Regex _regex_Alias = new System.Text.RegularExpressions.Regex(@".*\s+(?<alias>\S+)\s+(?i)on.*");
        internal object _Execute(System.Data.SqlClient.SqlConnection cn1, List<SqlReportColumn> columns, Dictionary<SqlReportColumn, System.Data.SqlClient.SortOrder> order, int? rowNumberStart = null, int? rowNumberEnd = null, bool isCountOnly = false)
        {
            if (NeedColumnData == null || columns == null || columns.Count == 0)
                return null;
            List<string> sqlWhere = new List<string>(SqlWhere);
            List<string> sqlSelect = new List<string>();
            Dictionary<string, string> sqlSelect_Alias = new Dictionary<string, string>();
            Dictionary<string, SqlJoinTypes> sqlJoin = new Dictionary<string, SqlJoinTypes>(SqlJoin);
            List<string> sqlJoinAliases = new List<string>();
            List<string> sqlDeclare = new List<string>(SqlDeclare);
            List<SqlReportColumn> _columns = new List<SqlReportColumn>(columns);
            Dictionary<string, string> _columns_SqlSelect = new Dictionary<string, string>();
            Dictionary<string, string> sqlJoinAliases_Changes = new Dictionary<string, string>();
            System.Text.RegularExpressions.Match match_Alias;
            string sqlJoinAlias;
            int i1;
            string s1;
            //
            if (order != null && order.Count > 0)
                _columns.AddRange(from x1 in order.Keys
                                  where !(from x2 in _columns
                                          select x2.Id).Contains(x1.Id)
                                  select x1);
            //
            foreach (SqlReportColumn feCol1 in _columns)
            {
                SqlReportNeedColumnDataEventArgs e = new SqlReportNeedColumnDataEventArgs(feCol1) { SqlRandomKey = SqlRandomKey };
                NeedColumnData(this, e);
                if (e.Ignore)
                    continue;
                //
                sqlDeclare.AddRange(from x1 in e.SqlDeclare
                                    where !sqlDeclare.Contains(x1)
                                    select x1);
                //
                sqlJoinAliases_Changes.Clear();
                foreach (string fe1 in e.SqlJoin.Keys)
                {
                    s1 = fe1;
                    //
                    foreach (var feChange in sqlJoinAliases_Changes)
                        s1 = s1.Replace(feChange.Key, feChange.Value);
                    //
                    if (e.SqlJoin_AutoNumberAliases)
                    {
                        match_Alias = _regex_Alias.Match(fe1);
                        if (match_Alias.Success)
                        {
                            sqlJoinAlias = match_Alias.Groups["alias"].Value;
                            i1 = 1;
                            while (sqlJoinAliases.Contains(sqlJoinAlias.ToLower()))
                            {
                                sqlJoinAlias = string.Format("{0}_{1}", match_Alias.Groups["alias"].Value, i1);
                                i1++;
                            }
                            //
                            sqlJoinAliases.Add(sqlJoinAlias.ToLower());
                            sqlJoinAliases_Changes.Add(match_Alias.Groups["alias"].Value, sqlJoinAlias);
                            //
                            s1 = s1.Replace(match_Alias.Groups["alias"].Value, sqlJoinAlias);
                        }
                    }
                    //
                    if (!sqlJoin.ContainsKey(s1))
                        sqlJoin.Add(s1, e.SqlJoin[fe1]);
                }
                //
                foreach (var feChange in sqlJoinAliases_Changes)
                    e.SqlSelect = e.SqlSelect.Replace(feChange.Key, feChange.Value);
                if (!sqlSelect.Contains(e.SqlSelect))
                    sqlSelect.Add(e.SqlSelect);
                _columns_SqlSelect.Add(feCol1.Id, e.SqlSelect);
                //
                foreach (string feWhere in e.SqlWhere)
                {
                    s1 = feWhere;
                    foreach (var feChange in sqlJoinAliases_Changes)
                        s1 = s1.Replace(feChange.Key, feChange.Value);
                    //
                    if (!sqlWhere.Contains(s1))
                        sqlWhere.Add(s1);
                }
            }
            //
            for (int iSelect = 0; iSelect < sqlSelect.Count; iSelect++)
                sqlSelect_Alias.Add(sqlSelect[iSelect], string.Format("Column_{0}", iSelect));
            string sql_0 = (from x1 in sqlSelect_Alias
                            select string.Format("({0}) AS {1}", x1.Key, x1.Value)).ToSeperated(",\n");
            //
            string sql_3 = "";
            if (order != null && order.Count > 0)
            {
                sql_3 = (from x1 in order
                         select string.Format("_SqlReport_A2.{0} {1}", sqlSelect_Alias[_columns_SqlSelect[x1.Key.Id]], (x1.Value == System.Data.SqlClient.SortOrder.Descending) ? "DESC" : "ASC")).ToSeperated(",");
                sql_3 = string.Format(",ROW_NUMBER() OVER(ORDER BY {0}) AS _SqlReport_RN", sql_3);
            }
            //
            string sql_1 = SqlFrom;
            foreach (var fe1 in sqlJoin)
            {
                switch (fe1.Value)
                {
                    case SqlJoinTypes.Inner:
                        sql_1 += "\nINNER JOIN\n";
                        break;
                    case SqlJoinTypes.LeftOuter:
                        sql_1 += "\nLEFT OUTER JOIN\n";
                        break;
                    case SqlJoinTypes.RightOuter:
                        sql_1 += "\nRIGHT OUTER JOIN\n";
                        break;
                    default:
                        break;
                }
                //
                sql_1 += "\n" + fe1.Key;
            }
            //
            string sql_2 = sqlWhere.ToSeperated("\nAND");
            //
            object r_Obj = null;
            //
            if (isCountOnly)
            {
                SqlCommandString sqlCM1 = new SqlCommandString();
                sqlCM1.CommandString = string.Format(@"
{2}
                SELECT
                    COUNT(*)
                FROM
                    {0}
                WHERE
                    {1}
", sql_1, sql_2, SqlInitialization);
                sqlCM1.Params = sqlDeclare;
                //
                System.Data.SqlClient.SqlCommand cm1 = new System.Data.SqlClient.SqlCommand(sqlCM1.GenerateSql(), cn1);
                if (BeforeExecute != null)
                {
                    SqlReportBeforeExecuteEventArgs e = new SqlReportBeforeExecuteEventArgs(cm1);
                    BeforeExecute(this, e);
                }
                //
                cm1.CommandTimeout = 3000;
                bool shouldCloseCN = (cn1.State == System.Data.ConnectionState.Closed);
                if (cn1.State != System.Data.ConnectionState.Open)
                    cn1.Open();
                //
                int r = 0;
                try
                {
                    r = (int)cm1.ExecuteScalar();
                    r_Obj = r;
                }
                catch (Exception)
                {
#if DEBUG
                    System.Diagnostics.Debug.Assert(false);
#endif
                }
                finally
                {
                    if (cn1 != null && shouldCloseCN)
                        cn1.Close();
                }
            }
            else
            {
                string sql_4 = "";
                if (order != null && order.Count > 0)
                {
                    if (rowNumberStart != null)
                    {
                        sql_4 = (rowNumberEnd == null) ? string.Format("_SqlReport_A1._SqlReport_RN >= {0}", rowNumberStart) : string.Format("_SqlReport_A1._SqlReport_RN BETWEEN {0} AND {1}", rowNumberStart, rowNumberEnd);
                        sql_4 = string.Format(@"
WHERE
    {0}", sql_4);
                    }
                    sql_4 += @"
ORDER BY
    _SqlReport_A1._SqlReport_RN";
                }
                //
                SqlCommandString sqlCM1 = new SqlCommandString();
                sqlCM1.CommandString = string.Format(@"
{5}
SELECT
    _SqlReport_A1.*
FROM
    (
        SELECT
            _SqlReport_A2.*
            {3}
        FROM
            (
                SELECT
                    {0}
                FROM
                    {1}
                WHERE
                    {2}
            ) AS _SqlReport_A2
    ) AS _SqlReport_A1
{4}", sql_0, sql_1, sql_2, sql_3, sql_4, SqlInitialization);
                sqlCM1.Params = sqlDeclare;
                //
                System.Data.SqlClient.SqlCommand cm1 = new System.Data.SqlClient.SqlCommand(sqlCM1.GenerateSql(), cn1);
                if (BeforeExecute != null)
                {
                    SqlReportBeforeExecuteEventArgs e = new SqlReportBeforeExecuteEventArgs(cm1);
                    BeforeExecute(this, e);
                }
                //
                cm1.CommandTimeout = 3000;
                bool shouldCloseCN = (cn1.State == System.Data.ConnectionState.Closed);
                if (cn1.State != System.Data.ConnectionState.Open)
                    cn1.Open();
                //
                List<SqlReportRow> r = new List<SqlReportRow>();
                System.Data.SqlClient.SqlDataReader dr1 = null;
                string col_Alias;
                int col_Ordinal;
                try
                {
                    dr1 = cm1.ExecuteReader();
                    while (dr1.Read())
                    {
                        SqlReportRow row1 = new SqlReportRow();
                        foreach (SqlReportColumn feCol in columns)
                        {
                            if (!_columns_SqlSelect.ContainsKey(feCol.Id) || !sqlSelect_Alias.ContainsKey(_columns_SqlSelect[feCol.Id]))
                                continue;
                            col_Alias = sqlSelect_Alias[_columns_SqlSelect[feCol.Id]];
                            col_Ordinal = dr1.GetOrdinal(col_Alias);
                            //
                            switch (feCol.DataType)
                            {
                                case SqlReportColumnDataTypes.Integer:
                                    row1.Values_Integer.Add(feCol.Id, (dr1.IsDBNull(col_Ordinal)) ? null : (int?)dr1.GetInt32(col_Ordinal));
                                    break;
                                case SqlReportColumnDataTypes.Double:
                                    row1.Values_Double.Add(feCol.Id, (dr1.IsDBNull(col_Ordinal)) ? null : (double?)dr1.GetDouble(col_Ordinal));
                                    break;
                                case SqlReportColumnDataTypes.Currency:
                                    row1.Values_Currency.Add(feCol.Id, new DataStructure.CurrencyValue(dr1.IsDBNull(col_Ordinal) ? null : (double?)dr1.GetDouble(col_Ordinal), feCol.DataType_Currency_DecimalCount, feCol.DataType_Currency_CultureInfo_Name));
                                    break;
                                case SqlReportColumnDataTypes.String:
                                    row1.Values_String.Add(feCol.Id, (dr1.IsDBNull(col_Ordinal)) ? "" : dr1.GetString(col_Ordinal));
                                    break;
                                case SqlReportColumnDataTypes.DateTime:
                                    row1.Values_DateTime.Add(feCol.Id, (dr1.IsDBNull(col_Ordinal)) ? null : (DateTime?)dr1.GetDateTime(col_Ordinal));
                                    break;
                                case SqlReportColumnDataTypes.Boolean:
                                    row1.Values_Boolean.Add(feCol.Id, (dr1.IsDBNull(col_Ordinal)) ? null : (bool?)dr1.GetBoolean(col_Ordinal));
                                    break;
                                case SqlReportColumnDataTypes.TimeSpan:
                                    row1.Values_TimeSpan.Add(feCol.Id, (dr1.IsDBNull(col_Ordinal)) ? null : (TimeSpan?)dr1.GetTimeSpan(col_Ordinal));
                                    break;
                                default:
                                    break;
                            }
                        }
                        //
                        r.Add(row1);
                    }
                }
                catch (Exception ex1)
                {
#if DEBUG
                    System.Diagnostics.Debug.Assert(false, "Execution error at: MA/Data/SqlReport.cs", ex1.ToString());
#endif
                }
                finally
                {
                    if (dr1 != null)
                    {
                        dr1.Close();
                        dr1.Dispose();
                    }
                    if (cn1 != null && shouldCloseCN)
                        cn1.Close();
                }
                //
                r_Obj = r;
            }
            //
            return r_Obj;
        }
    }
}
